﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.Sqlite;
using DbJobsApi.Extensions;
using DbJobsApi.Dto;
using DbJobsApi.Dto.DbConn;

namespace DbJobsApi.Controllers
{
    [Route("/api/[controller]/[action]")]
    [ApiController]
    public class DbConnController : ControllerBase
    {
        private string DbConnStr { get; set; }
        public DbConnController() 
        {
            var ApplicationData = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
            var apppath = Path.Combine(ApplicationData, "dbbox");
            if (!Directory.Exists(apppath))
            {
                Directory.CreateDirectory(apppath);
            }
            var dbpath = Path.Combine(apppath, "data.db");
            if(!Directory.Exists(apppath))
                System.IO.File.WriteAllText(dbpath, String.Empty);
            this.DbConnStr = $"Data Source={dbpath};";
            using var conn = new SqliteConnection(this.DbConnStr);
            conn.SqliteExcute(@"
CREATE TABLE IF NOT EXISTS setting
(
  `code` text not null PRIMARY key,
  `text` text null 
);
");
            conn.SqliteExcute(@"
CREATE TABLE IF NOT EXISTS conn(
   id   INTEGER PRIMARY KEY AUTOINCREMENT,
   name TEXT NOT NULL,
   data TEXT NOT NULL
);
");
            if (conn.SqliteQueryDataTable("select count(1) from setting").Rows[0][0].ToString() == "0")
            {
                conn.SqliteExcute("insert into setting(code, text) values(\"ver\",'0.1')");
            }
            if (conn.SqliteQueryDataTable("select count(1) from conn").Rows[0][0].ToString() == "0")
            {
                conn.SqliteExcute("insert into conn(name, data) values(\"UAT-IOT_PUB_MGT\",'{\"name\":\"UAT-IOT_PUB_MGT\",\"type\":\"Oracle\",\"server\":\"10.1.16.21\",\"port\":1521,\"connect_type\":\"servername\",\"servername\":\"iotuatdb.mindray.com\",\"username\":\"IOT_PUB_MGT\",\"password\":\"6T<4y827\"}')");
                conn.SqliteExcute("insert into conn(name, data) values(\"UAT-IVD\",'{\"name\":\"UAT-IVD\",\"type\":\"Oracle\",\"server\":\"10.1.16.21\",\"port\":1521,\"connect_type\":\"servername\",\"servername\":\"iotuatdb.mindray.com\",\"username\":\"IOT_IVD\",\"password\":\"FxW285bS\"}')");
                conn.SqliteExcute("insert into conn(name, data) values(\"UAT-SUB\",'{\"name\":\"UAT-SUB\",\"type\":\"Oracle\",\"server\":\"10.1.8.91\",\"port\":1521,\"connect_type\":\"sid\",\"sid\":\"iotsubdb\",\"username\":\"IOT_SUB_ALL\",\"password\":\"7@%<(Ci3\"}')");
                conn.SqliteExcute("insert into conn(name, data) values(\"mysqlite\",'{\"name\":\"mysqlite\",\"type\":\"Sqlite\",\"file\":\"d:\\\\temp.db\",\"version\":3}')");
                conn.SqliteExcute("insert into conn(name, data) values(\"mysqlite2\",'{\"name\":\"mysqlite2\",\"type\":\"Sqlite\",\"file\":\"d:\\\\temp2.db\",\"version\":3,\"username\":\"abc\",\"password\":\"123\"}')");
            }
        }
        [HttpPost]
        public FlagEntity GetConns()
        {
            using var conn = new SqliteConnection(this.DbConnStr);
            var dt = conn.SqliteQueryDataTable("select name,data from conn");
            return new ()
            { 
               flag = true,
               data = dt,
            };
        }

        [HttpPost]
        public FlagEntity Update(ConnEntity entity)
        {
            using var conn = new SqliteConnection(this.DbConnStr);

            var dict = new Dictionary<string, object>
            {
                { "@name", entity.name },
                { "@data", entity.data },
                { "@edit_name", entity.edit_name },
            };

            _ = conn.SqliteExcute("update conn set name=@name,data=@data where name=@edit_name", dict);
            return new()
            {
                flag = true,
                msg = "保存成功"
            };
        }

        [HttpPost]
        public FlagEntity Add(ConnEntity entity)
        {
            using var conn = new SqliteConnection(this.DbConnStr);

            var dict = new Dictionary<string, object>
            {
                { "@name", entity.name },
                { "@data", entity.data },
            };

            _ = conn.SqliteExcute("insert into conn(name, data) values(@name,@data)", dict);
            return new()
            {
                flag = true,
                msg = "保存成功"
            };
        }
        [HttpPost]
        public FlagEntity Delete(KeyEntity entity)
        {
            using var conn = new SqliteConnection(this.DbConnStr);

            var dict = new Dictionary<string, object>
            {
                { "@name", entity.name },
            };

            _ = conn.SqliteExcute("delete from conn where name = @name", dict);
            return new()
            {
                flag = true,
                msg = "保存成功"
            };
        }
    }
}
